﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ConnectDB
/// </summary>
public class ConnectDB
{
    public SqlConnection conn;
    public String order_id = "order by id";
    public String order_req = "order by request_code";
    public String order_code = "order by code";
    public String order_user = "order by username";
    public ConnectDB()
    {
        //
        // TODO: Add constructor logic here
        //
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["techdataconnection.Properties.Settings.techdataConnectionString"].ConnectionString);
    }

    public DataSet isExistUser(String strUserName)
    {
        String strQuery = "select * from User_Info where username='" + strUserName + "'";
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();

        return ds;
    }

    public DataSet getUser(UserInfo user)
    {
        String sqlQuery = "select * from User_Info where username='" + user.StrUserName + "' and password='" + user.StrPassWord + "'";
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();

        return ds;
    }

    public DataSet getDepartment(int departmentCode)
    {
        String sqlQuery = "select * from Department where id=" + departmentCode;
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();

        return ds;
    }

    public DataSet getPosition(int positionCode)
    {
        String sqlQuery = "select * from Position where id=" + positionCode;
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public DataSet getStatusUser(int statusCode)
    {
        String sqlQuery = "select * from Status where id=" + statusCode;
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public Boolean addUser(UserInfo ui)
    {
        String sqlQuery = "Insert Into User_Info(username, password, fullname, address, department, " +
                                                    "birthday, phone, position, email, status, scan_signature)" +
                           " values ('" + ui.StrUserName + "','" + ui.StrPassWord + "',N'" + ui.StrFullName + "',N'" + ui.StrAddress + "'," +
                           ui.DepDepartment.Id.ToString() + ",'" + ui.DateBirthday.ToShortDateString() + "','" + ui.StrPhone + "'," + ui.PosPosition.Id.ToString() + ",'" +
                           ui.StrEmail + "'," + ui.StaStatus.Id.ToString() + ",'" + ui.ImgSignature + "')";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }
    public DataSet getListDepartment()
    {
        String sqlQuery = "select * from Department " + order_id;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    public DataSet getListPosition()
    {
        String sqlQuery = "select * from Position " + order_id;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    public DataSet getListUser()
    {
        String sqlQuery = "select * from User_Info " + order_user;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        return ds;
    }
    public DataSet getRequestDetailList(String strRequestCode)
    {
        String sqlQuery = "select * from Request_Detail where request_code='" + strRequestCode + "' " + order_id;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();

        return ds;
    }

    public DataSet getAllRequests()
    {
        String sqlQuery = "select * from Request " + order_code;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public Boolean editUser(UserInfo ui)
    {
        String sqlQuery = "Update User_Info set password = '" + ui.StrPassWord + "', fullname= N'" + ui.StrFullName +
                            "', address = N'" + ui.StrAddress + "', department=" + ui.DepDepartment.Id.ToString() +
                            ", birthday='" + ui.DateBirthday.ToShortDateString() + "'," +
                            "phone = '" + ui.StrPhone + "', email = '" + ui.StrEmail + "'" +
                            ", scan_signature = '" + ui.ImgSignature + "' WHERE username = '" + ui.StrUserName + "'";

        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet getListBusCat()
    {
        String sqlQuery = "select * from Bus_Cataloge " + order_id;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public DataSet getListBusCat(String strIdCatBus)
    {
        String sqlQuery = "select * from Bus_Cataloge where id=" + strIdCatBus;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public DataSet getListPic()
    {
        String sqlQuery = "Select * from User_Info where position <> 1 and position <> 2";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public DataSet getListPic(String strIdPic)
    {
        String sqlQuery = "Select * from User_Info where position <> 1 and position <> 2 and UserName='" + strIdPic + "'";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public DataSet getListDirector()
    {
        String sqlQuery = "Select * from User_Info where position = 2";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    public DataSet getListDirector(String strIdDirector)
    {
        String sqlQuery = "Select * from User_Info where position = 2 and UserName='" + strIdDirector + "'";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    public DataSet getListVPre()
    {
        String sqlQuery = "Select * from User_Info where position = 1";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    public DataSet getListVPre(String strVPre)
    {
        String sqlQuery = "Select * from User_Info where position = 1 and Username='" + strVPre + "'";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    //get total request for user 
    // -1: null, other: total of request
    public Int32 getNumRequest(String uid)
    {
        String sqlQuery = "SELECT COUNT(confirm_by) AS total " +
                            "FROM Request_Confirm " +
                            "WHERE (confirm_by = '" + uid + "') AND (result = 4)" +
                            "GROUP BY confirm_by";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        int a = ds.Tables[0].Rows.Count;
        if (a > 0)
        {
            return Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
        }
        else return -1;

    }

    public DataSet getListPartner()
    {
        String sqlQuery = "select * from BP_Info " + order_code;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    public DataSet getListPartner(String strIdPartner)
    {
        String sqlQuery = "select * from BP_Info where code='" + strIdPartner + "'";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    public Boolean addBusParter(BusinessInfo bsiBusInfo)
    {
        String sqlQuery =
            "Insert Into " +
            "BP_Info(code, company, address, tel, fax, representative, cell_phone, website, email, input_by) " +
            "values ('" + bsiBusInfo.StrBusPartCode + "','" + bsiBusInfo.StrCompany + "','" + bsiBusInfo.StrAddress + "','" +
            bsiBusInfo.StrTel + "','" + bsiBusInfo.StrFax + "','" + bsiBusInfo.StrRepresentative + "','" + bsiBusInfo.StrCellPhone + "','" +
            bsiBusInfo.StrWebsite + "','" + bsiBusInfo.StrEmail + "','" + bsiBusInfo.UiInputBy.StrUserName + "')";

        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }
    public Boolean updateBusPartner(BusinessInfo bsiBusInfo)
    {
        String sqlQuery = "Update BP_Info set company='" + bsiBusInfo.StrCompany + "', address='" + bsiBusInfo.StrAddress + "', tel='" + bsiBusInfo.StrTel + "', " +
                            "fax='" + bsiBusInfo.StrFax + "', representative='" + bsiBusInfo.StrRepresentative + "', cell_phone='" + bsiBusInfo.StrCellPhone + "', " +
                            "website='" + bsiBusInfo.StrWebsite + "', email='" + bsiBusInfo.StrEmail + "', input_by='" + bsiBusInfo.UiInputBy.StrUserName + "' " +
                            "where code='" + bsiBusInfo.StrBusPartCode + "'";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public Boolean deleteBusPartner(String strCode)
    {
        String sqlQuery = "Delete from BP_Info where code='" + strCode + "'";

        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet getAllLastedRequests(String strCurrentDate)
    {
        String sqlQuery = "select * from Request where Code like 'RF-" + strCurrentDate + "-%' " + order_code;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }


    public Boolean saveRequest(RequestInfo rq)
    {
        String sqlQuery =
            "Insert Into " +
            "Request(username,code,description,date,amount,division,bp_user,pic,director,vpresident,status,remark)" +
            "values ('" + rq.UiSubmitter.StrUserName + "','" + rq.StrRequestCode + "','" + rq.StrDescription + "','" +
            rq.DtRequestDate + "'," + rq.FAmount.ToString() + "," + rq.BcBusCatDivision.Id.ToString() + ",'" +
            rq.BiBusPartner.StrBusPartCode + "','" + rq.UiPIC.StrUserName + "','" + rq.UiDirector.StrUserName + "','" +
            rq.UiVPre.StrUserName + "'," + rq.StRequestStatus.Id.ToString() + ",'" + rq.StrRemark + "')";

        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }
    public Boolean updateRequestInfo(RequestInfo rq)
    {
        String sqlQuery = "Update Request set description='" + rq.StrDescription + "'," +
            "date='" + rq.DtRequestDate.ToShortDateString() + "',amount=" + rq.FAmount.ToString() + "," +
            "division=" + rq.BcBusCatDivision.Id.ToString() + ",bp_user='" + rq.BiBusPartner.StrBusPartCode + "'," +
            "pic='" + rq.UiPIC.StrUserName + "',director='" + rq.UiDirector.StrUserName + "'," +
            "vpresident='" + rq.UiVPre.StrUserName + "',status='" + rq.StRequestStatus.Id.ToString() + "'," +
            "remark='" + rq.StrRemark + "' where code='" + rq.StrRequestCode + "'";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }
    public DataSet getInfoUser(String strUsername, String strEmail)
    {
        String sqlQuery = "select * from User_info where username = '" + strUsername + "' AND email = '" + strEmail + "'";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public Boolean saveRequestDetail(RequestDetailEntity rqd)
    {
        String sqlQuery = "Insert Into Request_Detail (request_code,id,date,voucher,description,quantity,amount_usd,ex_rate," +
                          "amount_vnd,vat,amount_vat,total) values ('" + rqd.RqRequestCode.StrRequestCode + "'," + rqd.ICodeDetail + ",'" +
                          rqd.DtDetailDate.ToShortDateString() + "','" + rqd.StrVoucher + "','" + rqd.StrDescription + "'," + rqd.FQuantity + "," +
                          rqd.FAmountUSD.ToString() + "," + rqd.FExRate.ToString() + "," + rqd.FAmountVND + "," + rqd.FVAT.ToString() + "," +
                          rqd.FAmountVAT.ToString() + "," + rqd.FTotal.ToString() + ")";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet saveRequestConfirm(RequestConfirm rc)
    {
        return null;
    }

    public Boolean updateRequestDetail(RequestDetailEntity rqd)
    {
        String sqlQuery = "Update Request_Detail set " +
                            "date='" + rqd.DtDetailDate.ToShortDateString() + "',voucher='" + rqd.StrVoucher + "', description='" +
                            rqd.StrDescription + "'," + "quantity=" + rqd.FQuantity.ToString() + ",amount_usd=" +
                            rqd.FAmountUSD.ToString() + ",ex_rate=" + rqd.FExRate.ToString() + ",amount_vnd=" +
                            rqd.FAmountVND.ToString() + ",vat=" + rqd.FVAT.ToString() + ", amount_vat=" +
                            rqd.FAmountVAT.ToString() + ", total=" + rqd.FTotal.ToString() +
                            " where request_code='" + rqd.RqRequestCode.StrRequestCode + "' and id=" + rqd.ICodeDetail;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public Boolean updateRequestConfirmStatus(int iStatus, String strRequestCode, int iCodeOfRequestDetail)
    {
        String sqlQuery = " Update Request_Confirm set result=" + iStatus.ToString() +
                            " Where request_code='" + strRequestCode + "' and id=" + iCodeOfRequestDetail.ToString();
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }
    public Boolean deleteRequestDetail(RequestDetailEntity rqd)
    {
        String sqlQuery = "Delete from Request_Confirm where request_code='" +
                            rqd.RqRequestCode.StrRequestCode + "' and id=" + rqd.ICodeDetail;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet getRequestInfo(String strRequestCode)
    {
        String sqlQuery = "Select * from Request where code='" + strRequestCode + "'";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    public DataSet getRequestInfo(int intStatus, String strConfirmBy)
    {
        String sqlQuery = "Select distinct Request.*, Request_Confirm.id " +
                            "From Request, Request_Confirm " +
                            "Where code=request_code and " + "Request_Confirm.result=" + intStatus.ToString() +
                                   " and confirm_by='" + strConfirmBy + "'";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    public DataSet getRequestConfirm(String strRequestCode)
    {
        String sqlQuery = "Select * from Request_Confirm where request_code = '" + strRequestCode + "'";
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public DataSet getBusCat()
    {
        String sqlQuery = "Select * from Bus_Cataloge " + order_id;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public Boolean saveBusCat(BusinessCataloge buscat)
    {

        String sqlQuery = "Insert Into Bus_Cataloge(catalog_name) values('" + buscat.StrCatalogeName.ToString() + "')";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet checkExistBusCat(String BusName)
    {
        String strQuery = "select * from Bus_Cataloge where catalog_name='" + BusName + "'";
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;

    }

    public Boolean deleteBusCataloge(BusinessCataloge buscat)
    {
        String sqlQuery = "Delete from Bus_Cataloge where id=" + buscat.Id;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public Boolean editBusCataloge(BusinessCataloge buscat)
    {
        String sqlQuery = "Update Bus_Cataloge set catalog_name = '" + buscat.StrCatalogeName.ToString() + "' where id = " + buscat.Id;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    //For Department
    public DataSet getDept()
    {
        String sqlQuery = "Select * from Department " + order_id;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public Boolean saveDept(Department dpm)
    {

        String sqlQuery = "Insert Into Department(department) values('" + dpm.StrDepartment.ToString() + "')";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet checkExistDept(String DeptName)
    {
        String strQuery = "select * from Department where department='" + DeptName + "'";
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;

    }

    public Boolean deleteDept(Department dpm)
    {
        String sqlQuery = "Delete from Department where id=" + dpm.Id;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public Boolean editDept(Department dpm)
    {
        String sqlQuery = "Update Department set department = '" + dpm.StrDepartment.ToString() + "' where id = " + dpm.Id;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    //For Position
    public Boolean savePos(Position pos)
    {

        String sqlQuery = "Insert Into Position(position_name) values('" + pos.StrPosition.ToString() + "')";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet checkExistPost(String PostName)
    {
        String strQuery = "select * from Position where position_name='" + PostName + "'";
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;

    }

    public Boolean deletePost(Position pos)
    {
        String sqlQuery = "Delete from Position where id=" + pos.Id;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public Boolean editPost(Position pos)
    {
        String sqlQuery = "Update Position set position_name = '" + pos.StrPosition.ToString() + "' where id = " + pos.Id;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    //For Status
    public DataSet getListStatus()
    {
        String sqlQuery = "Select * from Status " + order_id;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }
    public Boolean saveStatus(Status stt)
    {

        String sqlQuery = "Insert Into Status(status) values('" + stt.StrStatus.ToString() + "')";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet checkExistStatus(String StatusName)
    {
        String strQuery = "select * from Status where status='" + StatusName + "'";
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;

    }

    public Boolean deleteStatus(Status stt)
    {
        String sqlQuery = "Delete from Status where id=" + stt.Id;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public Boolean editStatus(Status stt)
    {
        String sqlQuery = "Update Status set status = '" + stt.StrStatus.ToString() + "' where id = " + stt.Id;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    //for Contact
    public Boolean addContact(EnContact ct)
    {
        String sqlQuery = "Insert Into Contact(name, address, phone, email, comment, status)" +
                            " values(N'" + ct.StrName + "',N'" + ct.StrAddress + "','" + ct.StrPhone + "','" + ct.StrEmail + "',N'" + ct.StrComment + "'," + ct.StaStatus.Id.ToString() + ")";

        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet getContactPending()
    {
        String strQuery = "select a.id, a.name, b.status from Contact AS a INNER JOIN" +
                            " Status AS b ON a.status = b.id ORDER BY a.id DESC";
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public DataSet getContact()
    {
        String strQuery = "select * from Contact " + order_id + " DESC";
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public Boolean addConfirmBy(String strRequestCode, String strOwner, int id, int iResult, DateTime date)
    {
        String sqlQuery = "Insert Into Request_Confrim(request_code, id, date, result, confirm_by)" +
            " values('" + strRequestCode + "'," + id.ToString() + ",'" + date.ToShortDateString() + "'," + iResult.ToString() + ",'" + strOwner + "')";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public Boolean updateConfirmBy(String strRequestCode, int id, int iResult, DateTime date)
    {
        String sqlQuery = "Update Request_Confirm set result=" + iResult.ToString() + ", date='" + date.ToShortDateString() + "'" +
                          " Where request_code='" + strRequestCode + "' and id=" + id.ToString();
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public Boolean updateRequestInfoStatus(String strRequestCode, int iStatus)
    {
        String sqlQuery = "Update Request set status=" + iStatus + " Where code='" + strRequestCode + "'";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public Boolean deleteContact(EnContact cont)
    {
        String sqlQuery = "Delete from Contact where id=" + cont.Id;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet getInfoContact(int id)
    {
        String sqlQuery = "select * from Contact where id = " + id;
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adap = new SqlDataAdapter(sqlQuery, conn);
        adap.Fill(ds);
        conn.Close();
        return ds;
    }

    public Boolean changeStatus(int id)
    {
        String sqlQuery = "Update Contact set status = 8 where id = " + id;
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }
}
